/*
 * 
 *
 * Created on March 22, 2007, 12:34 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

//package edu.byu.isys413.pb88.data;
package finalintex2web;
import java.util.*;
import java.io.*;
import java.sql.*;

/**
 *
 * @author craig
 */
public class ConceptualProductDAO extends ProductDAO 
{
    
    
    /** Creates a new instance of ConceptualProductDAO */
    public ConceptualProductDAO() 
    {
        super();
    }
    protected static ProductDAO instance = null;
        
    public static synchronized ProductDAO getInstance()
    {
        if (instance == null) {
            instance = new ConceptualProductDAO();
        }
        return instance;
    }
    
    public ProductBO create()
    {
        String id = GUID.generate();
        // super.create();
         ConceptualProductBO product = new  ConceptualProductBO(id);
        Cache c = Cache.getInstance();
        c.put(product.getId(),product);
        return product;
    }
    
    public ProductBO read(String id) throws DataException,ConnectionPoolException
    {
        
        try
        {
            Connection conn = ConnectionPool.getInstance().get();
            ProductBO prod = read(id,conn);
            ConnectionPool.getInstance().release(conn);
            return prod;
        }
        catch (SQLException e)
        {
            // rollback
            throw new DataException("SQL error from read 1", e);
        }
        catch (ConnectionPoolException e)
        {
            throw new ConnectionPoolException("Connection pool error in read 1", e);
        }
    }
    
    public ProductBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException{
     if(Cache.getInstance().containsKey(id)){
            ConceptualProductBO product = (ConceptualProductBO)Cache.getInstance().get(id);
            return product;
            } 
                
           try{        
           PreparedStatement stmt = conn.prepareStatement("SELECT * FROM conceptual WHERE conceptualid=?");
           stmt.setString(1,id);
           ResultSet rs =  stmt.executeQuery();
           
           ConnectionPool.getInstance().release(conn);
            
            if (rs.next()) 
            {
                ConceptualProductBO product = (ConceptualProductBO)ConceptualProductDAO.getInstance().create();
                Cache.getInstance().put(id,product);
                super.read(id);
                product.setDescription(rs.getString("Description"));
                product.setAverageCost(rs.getDouble("AverageCost"));
                Cache.getInstance().put(id,product);      
                product.setIndb(true);
                stmt.close();
                return product;
               

            }
            else
            {
                throw new DataException("ProductBO was not found in the database.");
            }
           
            }
           catch (ConnectionPoolException e)
           {
                throw new ConnectionPoolException("Could not retrieve a connection from the database", e);
            
            }
     catch (SQLException e){
                throw new DataException("SQL error", e);
            }
    }
    
     public synchronized void save(ProductBO prod) throws DataException,ConnectionPoolException 
     {
           
        try
        {
            Connection conn = ConnectionPool.getInstance().get();
            save(prod,conn);
            ConnectionPool.getInstance().release(conn);
        }
        catch (SQLException e)
        {
            //rollback
            throw new DataException("Could not retrieve record for id=" + prod.getId(), e);
            
         }
        catch (ConnectionPoolException e)
        {
            //throw new ConnectionPoolException("Could not retrieve record for id=" + e);
                e.printStackTrace();
        }
        
        // release the connection back to the pool
        return;
    }
     synchronized void save(ProductBO prod, Connection conn) throws SQLException, DataException, ConnectionPoolException 
     {
        // check the dirty flag in the RevenueSourceDBO.  if it is dirty, 
        // run update or insert
        
        if (prod.isDirty()) 
        {
       
             if (prod.isIndb()) 
            {
               System.out.println("in the update ");
                update(prod, conn);
            }
            else
            {
               System.out.println("Entering insert of conceptual");
               insert(prod, conn);
               prod.setIndb(true);
            }
            prod.setDirty(false);
        Cache.getInstance().touch(prod.getId());
     
        }
        
    }

    private synchronized void update(ProductBO prod, Connection conn) throws SQLException, DataException, ConnectionPoolException
    {
        PreparedStatement stmt = conn.prepareStatement("UPDATE conceptual SET productName=?,description=?,averageCost=? WHERE conceptualid=? ");
        super.save(prod,conn);
        ConceptualProductBO product = (ConceptualProductBO)prod;
        stmt.setString(1,product.getProductName());
        stmt.setString(2,product.getDescription());
        stmt.setDouble(3,product.getAverageCost());
        stmt.setString(4,product.getId());
        stmt.execute();
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        
    }
    private synchronized void insert(ProductBO prod, Connection conn) throws SQLException, DataException, ConnectionPoolException
    {
      
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO conceptual VALUES ( ?,?,?,?)");
        super.save(prod,conn);
        ConceptualProductBO product = (ConceptualProductBO)prod;
        stmt.setString(1,product.getId());
        stmt.setString(2,product.getProductName());
        stmt.setDouble(3,product.getAverageCost());
        stmt.setString(4,product.getDescription());
        stmt.execute();
        prod.setIndb(true);
        product.setIndb(true);
        stmt.close();
        // tell the RevenueSourceDBO that it's now in the db (so we call update next time not insert)
        
        ConnectionPool.getInstance().release(conn);
    }
    //////////Search method
    /*This method takes in category and spits out all the conceptual products in the category.
     *@param: category of type string
     *@return arraylist of conceptual products
     */
      public ArrayList<List> getAll(String category)
      {
            ArrayList<List> table = new ArrayList<List>();
            try
           {
                 Connection conn = ConnectionPool.getInstance().get();
                 PreparedStatement stmt = conn.prepareStatement("SELECT * from Conceptual co, category ca where co.categoryid = ca.categoryid and ca.type like ?" );
                 stmt.setString(1,category);
                 ResultSet rs = stmt.executeQuery();

                while(rs.next())
                {
                    ArrayList<String> row = new ArrayList<String>();
                    row.add(rs.getString("Description")); 
                    row.add(Double.toString((rs.getDouble("AverageCost")))); 
                    table.add(row);
                }
        
                ConnectionPool.getInstance().release(conn);
            }
            catch(ConnectionPoolException e){}
          catch(SQLException e){}
        
        return table;
    }

    
    
    
}